package org.lemon.cla.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.lemon.cla.pojo.ClubMember;
import org.lemon.cla.vo.ClubMemberVo; // 导入 ClubMemberVo
import org.lemon.cla.dto.request.ClubMemberSearchRequest;

@Mapper
public interface ClubMemberMapper extends BaseMapper<ClubMember> {

    // 联查用户表的公共字段片段
    String JOIN_USER_SELECT_FIELDS =
            "u.username AS username, " + // 注意别名与VO字段匹配
                    "u.avatar_url AS avatarUrl, " +
                    "u.real_name AS realName, " +
                    "u.phone AS phone, " +
                    "u.email AS email, " +
                    "u.role AS userRole, " + // 注意别名与VO字段匹配
                    "u.is_deleted AS userIsDeleted "; // Include is_deleted status

    /**
     * 根据成员ID获取社团成员详情，包含用户信息。
     * @param memberId 成员ID
     * @return 社团成员视图对象
     */
    @Select("SELECT cm.*, " +
            JOIN_USER_SELECT_FIELDS +
            "FROM club_member cm " +
            "LEFT JOIN user u ON cm.user_id = u.user_id " +
            "WHERE cm.member_id = #{memberId}")
    ClubMemberVo selectClubMemberVoById(@Param("memberId") Long memberId);

    /**
     * 分页查询所有社团成员，包含用户信息。
     * @param page 分页对象
     * @return 分页社团成员视图对象列表
     */
    @Select("SELECT cm.*, " +
            JOIN_USER_SELECT_FIELDS +
            "FROM club_member cm " +
            "LEFT JOIN user u ON cm.user_id = u.user_id " +
            "ORDER BY cm.join_time DESC")
    IPage<ClubMemberVo> selectClubMemberVoPage(IPage<ClubMemberVo> page);

    /**
     * 根据多个条件模糊查询社团成员，包含用户信息。
     * @param page 分页对象
     * @param req 查询请求DTO
     * @return 分页社团成员视图对象列表
     */
    @Select("<script>" +
            "SELECT cm.*, " +
            JOIN_USER_SELECT_FIELDS +
            "FROM club_member cm " +
            "LEFT JOIN user u ON cm.user_id = u.user_id " +
            "<where>" +
            "  <if test='req.userId != null'>" +
            "    AND cm.user_id = #{req.userId}" +
            "  </if>" +
            "  <if test='req.clubId != null'>" +
            "    AND cm.club_id = #{req.clubId}" +
            "  </if>" +
            "  <if test='req.role != null'>" +
            "    AND cm.role = #{req.role}" +
            "  </if>" +
            "  <if test='req.status != null'>" +
            "    AND cm.status = #{req.status}" +
            "  </if>" +
            "  <if test='req.joinTimeStart != null'>" +
            "    AND cm.join_time &gt;= #{req.joinTimeStart}" +
            "  </if>" +
            "  <if test='req.joinTimeEnd != null'>" +
            "    AND cm.join_time &lt;= #{req.joinTimeEnd}" +
            "  </if>" +
            "</where>" +
            "ORDER BY cm.join_time DESC" +
            "</script>")
    IPage<ClubMemberVo> selectClubMemberVoByMultiConditions(
            IPage<ClubMemberVo> page,
            @Param("req") ClubMemberSearchRequest request
    );
}